-- @owner:@m0_68540079
-- @date:2025/6/25
-- @testpoint:NLS_LOWER在不同使用存储例程（Stored Routines）中的使用情况
--step1：在函数体中使用NLS_LOWER函数；expect：正常输出或合理报错
-- 如果已存在该函数，先删除
DROP FUNCTION IF EXISTS nls_lower_func(text);
-- 创建函数
CREATE OR REPLACE FUNCTION nls_lower_func(input_string TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN nls_lower(input_string);  -- PostgreSQL 的 lower() 函数
END;
$$ LANGUAGE plpgsql;

SELECT nls_lower_func('АаБбВвГгДдЂђЕеЖ⚗ИиЈјКкЛлЉљМмНнЊњОоПпРрСсТтЋћУуФфХхЦцЧчЏџШш');

DROP FUNCTION IF EXISTS nls_lower_func;

--step2：在prepare中使用NLS_LOWER函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
                           employee_id NUMBER PRIMARY KEY,
                           first_name VARCHAR2(50),
                           last_name VARCHAR2(50),
                           salary NUMBER
);
INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (1,'JOHN', 'DOE',123);
INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (2,'JANE', 'SMITH',234);
PREPARE search_employee (TEXT) AS
    SELECT * FROM employees WHERE NLS_LOWER(last_name ) = NLS_LOWER($1);
EXECUTE search_employee('DOE');
DROP TABLE IF EXISTS employees;
